package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.LinkedList;

import ModelLayer.RBDate;

import ModelLayer.RoomType;

public class DBRBDate implements IFDBRBDate {
	private Connection con;

	public DBRBDate() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public RBDate getDate(int ryId, Date date, boolean retrieveAssociation) {
		String wClause = "  roomT_Id = '" + ryId + "' and" + " bookingDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(date)) + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertDate(RBDate date) {
		int rc = -1;
		String query = "INSERT INTO bookingYear(roomT_Id, bookingDate, nrOfAvailableRoom) VALUES('"
				+ date.getRoomType().getId()
				+ "','"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(date.getDate()))
				+ "','" + date.getNrOfAvailableRoom() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("BookingYear date is not inserted");
		}

		return rc;
	}

	@Override
	public int deleteDate(int ryId, Date date) {
		int rc = -1;
		synchronized (this) {
			String query = "DELETE FROM bookingYear WHERE roomT_Id = '" + ryId
					+ "' and" + " bookingDate = '"
					+ (new SimpleDateFormat("dd/MM/yyyy").format(date)) + "'";
			System.out.println(query);
			try {
				Statement stmt = con.createStatement();
				stmt.setQueryTimeout(5);
				rc = stmt.executeUpdate(query);
				stmt.close();
			} catch (Exception ex) {
				System.out.println("Delete exception in bookingYear date db: "
						+ ex);
			}
		}
		return rc;
	}

	@Override
	public int updateDate(RBDate date) {
		int rc = -1;
		RBDate dateObj = date;
		String query = "UPDATE bookingYear SET "
				+ "nrOfAvailableRoom = '"
				+ dateObj.getNrOfAvailableRoom()
				+ "'"
				+ " WHERE roomT_Id = '"
				+ dateObj.getRoomType().getId()
				+ "' and"
				+ " bookingDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(dateObj.getDate()))
				+ "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in bookingYear date db: " + e);
		}
		return rc;
	}

	@Override
	public LinkedList<RBDate> getDatesForRT(int rtId,
			boolean retrieveAssociation) {
		return miscWhere(" roomT_Id = '" + rtId + "'", retrieveAssociation);
	}

	@Override
	public Date getLastDate(int rtId) {
		ResultSet results;
		Date lastDate = null;
		String query = "Select max(bookingDate) From bookingYear Where roomT_Id = '"
				+ rtId + "'";
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				lastDate = results.getTimestamp("bookingDate");
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return lastDate;
	}

	private RBDate singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;

		RBDate date = new RBDate();
		String query = buildQuery(wClause);
		// System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				date = buildRBDate(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBRoomType dbRoomType = new DBRoomType();
					RoomType roomType = dbRoomType.getRoomType(date
							.getRoomType().getId());
					date.setRoomType(roomType);
					System.out.println("Roomtype is selected");
				}
			} else {
				date = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return date;
	}

	private LinkedList<RBDate> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		LinkedList<RBDate> list = new LinkedList<RBDate>();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				RBDate date = new RBDate();
				date = buildRBDate(results);
				list.add(date);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (RBDate date : list) {
					IFDBRoomType dbRoomType = new DBRoomType();
					RoomType roomType = dbRoomType.getRoomType(date
							.getRoomType().getId());
					date.setRoomType(roomType);
					System.out.println("Roomtype is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private RBDate buildRBDate(ResultSet results) {
		RBDate date = new RBDate();
		try {
			date.setNrOfAvailableRoom(results.getInt("nrOfAvailableRoom"));
			date.setDate(new SimpleDateFormat("dd/MM/yyyy").parse(results
					.getString("bookingDate")));
			date.setRoomType(new RoomType(results.getInt("roomT_Id")));
		} catch (Exception e) {
			System.out.println("error in building the rbDate object");
		}
		return date;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT roomT_Id, bookingDate, nrOfAvailableRoom FROM bookingYear";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
